
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 03/16/2010 20:15:31
-- Generated from EDMX file: C:\Users\Dave\Documents\Visual Studio 2010\Projects\KharaPos\KharaPosData\Entities\KharaPosDataContext.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [kharapos];
GO
IF SCHEMA_ID(N'Catalog') IS NULL EXECUTE(N'CREATE SCHEMA [Catalog]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[Catalog].[FK_BusinessUnits_BusinessUnits]', 'F') IS NOT NULL
    ALTER TABLE [Catalog].[BusinessUnits] DROP CONSTRAINT [FK_BusinessUnits_BusinessUnits];
GO
IF OBJECT_ID(N'[Catalog].[FK_Catalogs_BusinessUnits]', 'F') IS NOT NULL
    ALTER TABLE [Catalog].[CatalogEntries] DROP CONSTRAINT [FK_Catalogs_BusinessUnits];
GO
IF OBJECT_ID(N'[Catalog].[FK_CatalogDescription_Catalogs]', 'F') IS NOT NULL
    ALTER TABLE [Catalog].[CatalogDetails] DROP CONSTRAINT [FK_CatalogDescription_Catalogs];
GO
IF OBJECT_ID(N'[Catalog].[FK_CatalogDescriptions_CategoryDescriptions]', 'F') IS NOT NULL
    ALTER TABLE [Catalog].[CatalogDetails] DROP CONSTRAINT [FK_CatalogDescriptions_CategoryDescriptions];
GO
IF OBJECT_ID(N'[Catalog].[FK_Catalogs_ItemDescriptions]', 'F') IS NOT NULL
    ALTER TABLE [Catalog].[CatalogEntries] DROP CONSTRAINT [FK_Catalogs_ItemDescriptions];
GO
IF OBJECT_ID(N'[Catalog].[FK_CategoryDescriptions_CategoryDescriptions]', 'F') IS NOT NULL
    ALTER TABLE [Catalog].[Categories] DROP CONSTRAINT [FK_CategoryDescriptions_CategoryDescriptions];
GO
IF OBJECT_ID(N'[Catalog].[FK_Products_CategoryDescriptions]', 'F') IS NOT NULL
    ALTER TABLE [Catalog].[Products] DROP CONSTRAINT [FK_Products_CategoryDescriptions];
GO
IF OBJECT_ID(N'[Catalog].[FK_ProductUpc]', 'F') IS NOT NULL
    ALTER TABLE [Catalog].[Upcs] DROP CONSTRAINT [FK_ProductUpc];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[Catalog].[BusinessUnits]', 'U') IS NOT NULL
    DROP TABLE [Catalog].[BusinessUnits];
GO
IF OBJECT_ID(N'[Catalog].[CatalogDetails]', 'U') IS NOT NULL
    DROP TABLE [Catalog].[CatalogDetails];
GO
IF OBJECT_ID(N'[Catalog].[CatalogEntries]', 'U') IS NOT NULL
    DROP TABLE [Catalog].[CatalogEntries];
GO
IF OBJECT_ID(N'[Catalog].[Categories]', 'U') IS NOT NULL
    DROP TABLE [Catalog].[Categories];
GO
IF OBJECT_ID(N'[Catalog].[Products]', 'U') IS NOT NULL
    DROP TABLE [Catalog].[Products];
GO
IF OBJECT_ID(N'[Catalog].[Upcs]', 'U') IS NOT NULL
    DROP TABLE [Catalog].[Upcs];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'BusinessUnits'
CREATE TABLE [Catalog].[BusinessUnits] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ParentId] int  NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'CatalogDetails'
CREATE TABLE [Catalog].[CatalogDetails] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CatalogId] int  NOT NULL,
    [CategoryId] int  NOT NULL,
    [CatalogName] nvarchar(50)  NOT NULL,
    [CatalogDescription] nvarchar(256)  NOT NULL,
    [CatalogShortDescription] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'CatalogEntries'
CREATE TABLE [Catalog].[CatalogEntries] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [SKU] nvarchar(50)  NOT NULL,
    [ProductId] int  NOT NULL,
    [BusinessUnitId] int  NOT NULL,
    [DateAdded] datetime  NOT NULL,
    [DateAvailableInternal] datetime  NULL,
    [DateAvailablePublic] datetime  NULL,
    [DateDiscontinuedInternal] datetime  NULL,
    [DateDiscontinuedExternal] datetime  NULL
);
GO

-- Creating table 'Categories'
CREATE TABLE [Catalog].[Categories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ParentId] int  NULL,
    [Name] nvarchar(50)  NOT NULL,
    [ShortDescription] nvarchar(100)  NOT NULL,
    [LongDescription] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'Products'
CREATE TABLE [Catalog].[Products] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CategoryId] int  NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Description] nvarchar(256)  NOT NULL,
    [ShortDescription] nvarchar(100)  NOT NULL,
    [ManufacturerId] int  NOT NULL
);
GO

-- Creating table 'Upcs'
CREATE TABLE [Catalog].[Upcs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UpcNumber] nvarchar(max)  NOT NULL,
    [ProductId] int  NOT NULL
);
GO

-- Creating table 'ManuFacturers'
CREATE TABLE [Catalog].[ManuFacturers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'BusinessUnits'
ALTER TABLE [Catalog].[BusinessUnits]
ADD CONSTRAINT [PK_BusinessUnits]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CatalogDetails'
ALTER TABLE [Catalog].[CatalogDetails]
ADD CONSTRAINT [PK_CatalogDetails]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CatalogEntries'
ALTER TABLE [Catalog].[CatalogEntries]
ADD CONSTRAINT [PK_CatalogEntries]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Categories'
ALTER TABLE [Catalog].[Categories]
ADD CONSTRAINT [PK_Categories]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Products'
ALTER TABLE [Catalog].[Products]
ADD CONSTRAINT [PK_Products]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Upcs'
ALTER TABLE [Catalog].[Upcs]
ADD CONSTRAINT [PK_Upcs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ManuFacturers'
ALTER TABLE [Catalog].[ManuFacturers]
ADD CONSTRAINT [PK_ManuFacturers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ParentId] in table 'BusinessUnits'
ALTER TABLE [Catalog].[BusinessUnits]
ADD CONSTRAINT [FK_BusinessUnits_BusinessUnits]
    FOREIGN KEY ([ParentId])
    REFERENCES [Catalog].[BusinessUnits]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BusinessUnits_BusinessUnits'
CREATE INDEX [IX_FK_BusinessUnits_BusinessUnits]
ON [Catalog].[BusinessUnits]
    ([ParentId]);
GO

-- Creating foreign key on [BusinessUnitId] in table 'CatalogEntries'
ALTER TABLE [Catalog].[CatalogEntries]
ADD CONSTRAINT [FK_Catalogs_BusinessUnits]
    FOREIGN KEY ([BusinessUnitId])
    REFERENCES [Catalog].[BusinessUnits]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Catalogs_BusinessUnits'
CREATE INDEX [IX_FK_Catalogs_BusinessUnits]
ON [Catalog].[CatalogEntries]
    ([BusinessUnitId]);
GO

-- Creating foreign key on [CatalogId] in table 'CatalogDetails'
ALTER TABLE [Catalog].[CatalogDetails]
ADD CONSTRAINT [FK_CatalogDescription_Catalogs]
    FOREIGN KEY ([CatalogId])
    REFERENCES [Catalog].[CatalogEntries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CatalogDescription_Catalogs'
CREATE INDEX [IX_FK_CatalogDescription_Catalogs]
ON [Catalog].[CatalogDetails]
    ([CatalogId]);
GO

-- Creating foreign key on [CatalogId] in table 'CatalogDetails'
ALTER TABLE [Catalog].[CatalogDetails]
ADD CONSTRAINT [FK_CatalogDescriptions_CategoryDescriptions]
    FOREIGN KEY ([CatalogId])
    REFERENCES [Catalog].[Categories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CatalogDescriptions_CategoryDescriptions'
CREATE INDEX [IX_FK_CatalogDescriptions_CategoryDescriptions]
ON [Catalog].[CatalogDetails]
    ([CatalogId]);
GO

-- Creating foreign key on [ProductId] in table 'CatalogEntries'
ALTER TABLE [Catalog].[CatalogEntries]
ADD CONSTRAINT [FK_Catalogs_ItemDescriptions]
    FOREIGN KEY ([ProductId])
    REFERENCES [Catalog].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Catalogs_ItemDescriptions'
CREATE INDEX [IX_FK_Catalogs_ItemDescriptions]
ON [Catalog].[CatalogEntries]
    ([ProductId]);
GO

-- Creating foreign key on [ParentId] in table 'Categories'
ALTER TABLE [Catalog].[Categories]
ADD CONSTRAINT [FK_CategoryDescriptions_CategoryDescriptions]
    FOREIGN KEY ([ParentId])
    REFERENCES [Catalog].[Categories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategoryDescriptions_CategoryDescriptions'
CREATE INDEX [IX_FK_CategoryDescriptions_CategoryDescriptions]
ON [Catalog].[Categories]
    ([ParentId]);
GO

-- Creating foreign key on [CategoryId] in table 'Products'
ALTER TABLE [Catalog].[Products]
ADD CONSTRAINT [FK_Products_CategoryDescriptions]
    FOREIGN KEY ([CategoryId])
    REFERENCES [Catalog].[Categories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Products_CategoryDescriptions'
CREATE INDEX [IX_FK_Products_CategoryDescriptions]
ON [Catalog].[Products]
    ([CategoryId]);
GO

-- Creating foreign key on [ProductId] in table 'Upcs'
ALTER TABLE [Catalog].[Upcs]
ADD CONSTRAINT [FK_ProductUpc]
    FOREIGN KEY ([ProductId])
    REFERENCES [Catalog].[Products]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductUpc'
CREATE INDEX [IX_FK_ProductUpc]
ON [Catalog].[Upcs]
    ([ProductId]);
GO

-- Creating foreign key on [ManufacturerId] in table 'Products'
ALTER TABLE [Catalog].[Products]
ADD CONSTRAINT [FK_ManuFacturerProduct]
    FOREIGN KEY ([ManufacturerId])
    REFERENCES [Catalog].[ManuFacturers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ManuFacturerProduct'
CREATE INDEX [IX_FK_ManuFacturerProduct]
ON [Catalog].[Products]
    ([ManufacturerId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------